package com.ado.agea.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ConnectionManager {
	

	private static Connection getConnection(String dsn) {
		String url = "jdbc:odbc:" + dsn; // "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\\Rubros.mdb";
		String username = "";
		String password = "";
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		} catch (ClassNotFoundException e) {

			e.printStackTrace();
		}
		try {
			return DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	private static Connection getMysqlConnection(String dsn) {
		String url = "jdbc:odbc:" + dsn; // "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\\Rubros.mdb";
		String username = "";
		String password = "";
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		} catch (ClassNotFoundException e) {

			e.printStackTrace();
		}
		try {
			return DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public static List<Map> getResultset(Bases base,String sql) {
		try {
			Connection conn;
			Statement st;
			ResultSet rs;
			ResultSetMetaData metadata;
			int columns;
			List<Map> list; 
			
			conn = getConnection(base.getArchivo());
			st = conn.createStatement();
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			metadata = rs.getMetaData();
			
			columns = metadata.getColumnCount();
			list = new ArrayList<Map>();
			
			while (rs.next()) {
				Map<String,String> row = new HashMap<String,String>(columns);
				for (int i = 1; i <= columns; ++i) {
					row.put(metadata.getColumnName(i), rs.getString(i));
				}
				list.add(row);
			}
			st.close(); 
			conn.close();
			
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public static List<Map> cargarBase(Bases base) {
		String sql= "SELECT * FROM conceptos";
		return ConnectionManager.getResultset(base,sql);
	}
}
